﻿package com.eleme.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import com.eleme.dao.ISampleDao;
import com.eleme.entity.Sample;


/** 
 * ClassName: SampleDao <br/> 
 * Function: Dao实现类模板 <br/> 
 * Reason: 这个模板提供了各种方法的重写. <br/> 
 * date: 2017年12月19日 下午4:44:36 <br/> 
 * 
 * @author Pan 
 * @version  
 * @since JDK 1.8 
 */  
public class SampleDao extends BaseDao<Sample> implements ISampleDao{

	
	//TODO: 修改返回类型为对应的实体类
	public Sample createEntity() {
		return new Sample();
	}
	
	

	public List<Sample> getSamples() {
		PreparedStatement ps = null;
		ResultSet rs = null;
		int currentid = -1;
		List<Sample> li = new ArrayList<Sample>();
		Sample sa = null;
		StringBuilder sb = new StringBuilder();
		sb.append("Select m.sampleid mid, m.samplename mname, s.sampleid sid, s.samplename sname"
				+ " From Sample m, Sample s Where s.parentid = m.sampleid Order By m.sampleid");
		LOGGER.debug(sb.toString());
		try {
			ps = getConnection().prepareStatement(sb.toString());
			rs = ps.executeQuery();
			while(rs != null && rs.next()) {
				if(currentid != rs.getInt("mid")) {
					sa = new Sample();
					li.add(sa);
					currentid = rs.getInt("mid");
					sa.setSampleid(currentid);
					sa.setSamplename(rs.getString("mname"));
					sa.setLstSample(new ArrayList<Sample>());
				}

				Sample so = new Sample();
				so.setSampleid(currentid);
				so.setSamplename(rs.getString("mname"));	
				sa.getLstSample().add(so);
			}
		} catch (SQLException e) {
			LOGGER.error("getSampleJSON出错：" + e.getMessage());
			e.printStackTrace();
		}
		
		
		return li;
	}



	@Override
	public Sample createEntity(ResultSet rs) {
		//todo：如有必要应重写生成实体的方法
		return super.createEntity(rs);
	}

	@Override
	public int insert(Sample entity) {
		//return super.insert(entity);
		//todo：需要重写此的方法，只插入必要字段
		PreparedStatement ps = null;
		int res = -1;
		String sql = "Insert into Sample(samplename , parentid ,level) values(?,?,?)";
		/*
		 * 此例中，希望主键sampleid由数据库自行递增，保证主键不冲突，因此重写此方法，否则应提供构造不重复主键的方法。
		 * 注意主键sampleid必须设置为auto_increment，否则语句执行将报错。
		 */
		
		try {
			try {
				LOGGER.debug("insert：" + sql);
				ps = getConnection().prepareStatement(sql);
				ps.setString(1, entity.getSamplename());
				ps.setInt(2, entity.getParentid());
				ps.setInt(3, entity.getLevel());
				res = ps.executeUpdate();
			} finally {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			LOGGER.error("添加Sample表出错，SQL=" + sql + "，错误信息：" + e.getMessage());
		}
		
		return res;
	}

	@Override
	public int delete(int id) {
		//return super.delete(id);
		//todo：如有必要应重写此的方法 例如：主键名称不叫id时
		PreparedStatement ps = null;
		int res = -1;
		String sql = "Delete From Sample Where sampleid = ?";
		try {
			LOGGER.debug("delete：" + sql);
			ps = getConnection().prepareStatement(sql);
			ps.setInt(1, id);
			res = ps.executeUpdate();
		} catch (SQLException e) {
			LOGGER.error("表Sample删除失败：" + sql + "，错误信息：" + e.getMessage());
			e.printStackTrace();
		}
		
		return res;
	}

	@Override
	public int update(Sample entity, int id) {
		//return super.update(entity, id);
		//todo：需要重写此方法，只更新必要字段
		PreparedStatement ps = null;
		int res = -1;
		String sql = "Update Sample Set samplename = ?, parentid = ?,level = ? Where sampleid = ?";
		
		try {
			try {
				LOGGER.debug("update：" + sql);
				ps = getConnection().prepareStatement(sql);
				ps.setString(1, entity.getSamplename());
				ps.setInt(2, entity.getParentid());
				ps.setInt(3, entity.getLevel());
				ps.setInt(4, id);
				res = ps.executeUpdate();
			} finally {
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			LOGGER.error("更新Sample表出错，SQL=" + sql + "，错误信息：" + e.getMessage());
		}
		
		return res;
	}

	@Override
	public Sample getById(int id) {
		//return super.getById(id);
		//todo：如有必要应重写此的方法。例如：主键名称不同
		PreparedStatement ps = null;
		ResultSet rs = null;
		Sample res = null;
		String sql = "Select * from Sample Where sampleid= ?";
		try {
			try {
				LOGGER.debug("getById：" + sql);
				ps = getConnection().prepareStatement(sql);
				ps.setInt(1, id);
				rs = ps.executeQuery();
				if (rs != null && rs.next()) {
					res = new Sample();
					res.setSampleid(rs.getInt("sampleid"));
					res.setSamplename(rs.getString("samplename"));
					res.setParentid(rs.getInt("parentid"));
					res.setLevel(rs.getInt("level"));
				} 
			} finally {
				rs.close();
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			LOGGER.error("取Sample表数据出错，SQL=" + sql + "，错误信息：" + e.getMessage());
		}
		
		//此例中忽略了子类型列表 
		return res;
	}
	
	public static void main(String[] args) {
		//TODO: 此处放置测试代码，用于测试本Dao工作是否正常
		SampleDao dao = new SampleDao();
		Sample sa = dao.createEntity();
		sa.setSamplename("样例5");
		sa.setParentid(2);
		List<Sample> li = dao.getAll();
		for(Iterator<Sample> it = li.iterator(); it.hasNext(); ) {
			sa = it.next();
			System.out.println(sa.getSampleid() + " " + sa.getSamplename());
		}
		sa = dao.getById(5);
		System.out.println(sa.getSamplename());
	}

}
